package com.qlzb.taskmanagement.utils;


/**
 *
 */
import com.qlzb.taskmanagement.persistentData.entity.OperationStation;
import com.qlzb.taskmanagement.vo.quartzVo.ExcelCols;
import com.qlzb.taskmanagement.vo.quartzVo.ExcelRows;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;


/**
 * @author zhangshoubing
 * @created 2017-03-09
 */
public   class ReadExcel {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static final String POINT = ".";

    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";
    public static final String PROCESSING = "Processing...";



    /**
     * read the Excel file
     * @param path the path of the Excel file
     *    @param         iscontalTitle 是否包含标题
     * @return
     * @throws IOException
     */
    public static Map<String,List<ExcelRows>> readExcel(String path, boolean iscontalTitle, int skipRows) throws IOException {
        if (path == null || "".equals(path)) {
            return null;
        } else {
            String postfix = getPostfix(path);
            if (!"".equals(postfix)) {
                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
                    return readXls(path,iscontalTitle,skipRows);
                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
                    return readXlsx(path,iscontalTitle,skipRows);
                }
            } else {
                System.out.println(path + NOT_EXCEL_FILE);
            }
        }
        return null;
    }

    /**
     * Read the Excel 2003-2007
     * path 路径
     * iscontalTitle 是否包含标题
     * @return
     * @throws IOException
     */
    public static Map<String,List<ExcelRows>> readXls(String path, boolean iscontalTitle, int skipRows) throws IOException {
        System.out.println(PROCESSING + path);
        InputStream is = new FileInputStream(path);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

        Map<String,List<ExcelRows>> sheetMap=new LinkedHashMap<String, List<ExcelRows>>();

        // Read the Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            List<ExcelRows> list = new ArrayList<ExcelRows>();
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null||hssfSheet.getLastRowNum()==0) {
                continue;
            }

            //第一行的总列数
            HSSFRow firstxssfRow = hssfSheet.getRow(skipRows);
            //获取总列数
            int columnNum=firstxssfRow.getPhysicalNumberOfCells();
            // Read the Row
            for (int rowNum = skipRows; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                if(iscontalTitle&rowNum==skipRows){
                    continue;
                    }

                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    ExcelRows excelRow = new ExcelRows();
                    HSSFCell firstxSSFCell = hssfRow.getCell(0);
                    if(firstxSSFCell==null){
                        continue;
                    }
                    for(int  i=0;i<columnNum;i++ ){
                        HSSFCell hSSFCell = hssfRow.getCell(i);
                        ExcelCols excelCols=new ExcelCols();
                        excelCols.setIndex(i);
                        excelCols.setValue(getValue(hSSFCell));
                        if(iscontalTitle){
                            setCellTitleForHSSFCell(excelCols, hssfSheet.getRow(skipRows).getCell(i));
                        }
                        excelRow.addColses(excelCols);
                    }
                    list.add(excelRow);

                }
            }
            sheetMap.put("sheet"+numSheet,list);
        }
        return sheetMap;
    }
    /**
     * Read the Excel 2010
     * @param path the path of the excel file
     * path 路径
     * iscontalTitle 是否包含标题
     * 跳过前面几行
     * @return
     * @throws IOException
     */
    public static Map<String,List<ExcelRows>> readXlsx(String path, boolean iscontalTitle, int skipRows) throws IOException {
        System.out.println(PROCESSING + path);
        XSSFWorkbook xssfWorkbook = null;
        try {
            InputStream is = new FileInputStream(path);
            xssfWorkbook = new XSSFWorkbook(is);
        } catch (Exception e) {
            e.printStackTrace();
        }
        Map<String,List<ExcelRows>> sheetMap=new LinkedHashMap<String, List<ExcelRows>>();
        // Read the Sheet
        for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
            List<ExcelRows> list = new ArrayList<ExcelRows>();
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
            if (xssfSheet == null||xssfSheet.getLastRowNum()==0) {
                continue;
            }
            //第一行的总列数
            XSSFRow firstxssfRow = xssfSheet.getRow(skipRows);
            //获取总列数
            int columnNum=firstxssfRow.getPhysicalNumberOfCells();

            // Read the Row
            for (int rowNum = skipRows; rowNum <=xssfSheet.getLastRowNum(); rowNum++) {
                if(iscontalTitle&rowNum==skipRows){
                    continue;
                }
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                if (xssfRow != null) {
                    ExcelRows excelRow = new ExcelRows();
                    XSSFCell firstxSSFCell = xssfRow.getCell(0);
                    if(firstxSSFCell==null){
                        continue;
                    }
                    for(int  i=0;i<columnNum;i++ ){
                        ExcelCols excelCols=new ExcelCols();
                        XSSFCell xSSFCell = xssfRow.getCell(i);
                        excelCols.setIndex(i);
                        excelCols.setValue(getValue(xSSFCell));
                        if(iscontalTitle){
                            setCellTitleForXSSFCell(excelCols,xssfSheet.getRow(skipRows).getCell(i));
                        }
                        excelRow.addColses(excelCols);

                    }

                    list.add(excelRow);
                }
            }
            sheetMap.put("sheet"+numSheet,list);
        }
        return sheetMap;
    }
    //获取每一列的标题
    public  static ExcelCols setCellTitleForXSSFCell(ExcelCols excelCols, XSSFCell titleXSSFCell){
        excelCols.setTitle(getValue(titleXSSFCell));
        return excelCols;
    }
    //获取每一列的标题
    public  static ExcelCols setCellTitleForHSSFCell(ExcelCols excelCols, HSSFCell titleXSSFCell){
        excelCols.setTitle(getValue(titleXSSFCell));
        return excelCols;
    }


    @SuppressWarnings("static-access")
    private static String getValue(XSSFCell xssfRow) {
        if(xssfRow==null){
            return null;
        }
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }
    @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell) {
        if(hssfCell==null){
            return null;
        }
        if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
            return String.valueOf(hssfCell.getStringCellValue());
        }
    }

    /**
     * get postfix of the path
     * @param path
     * @return
     */
    public static String getPostfix(String path) {
        if (path == null || "".equals(path.trim())) {
            return "";
        }
        if (path.contains(POINT)) {
            return path.substring(path.lastIndexOf(POINT) + 1, path.length());
        }
        return "";
    }

    public static void main(String[] args)  {
        try{
            Map<String,List<ExcelRows>> sheetMap= ReadExcel.readExcel("D:/yurun/02开发/crawler-web/excelFile/site/达州2018-09-11_14_48_20.xls", true,1);
            List<ExcelRows> listExcelRows=sheetMap.get("sheet0");
            List<OperationStation> list=new ArrayList<>();
            for(ExcelRows excelRow:listExcelRows){
                OperationStation operationStation= (OperationStation) CommonBeanUtils.encapsulation(excelRow,OperationStation.class);
                System.out.println(operationStation);
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }
}
